#/bin/bash
# autor:duhuan time 2022-05-13
# 用于全量导入

reg='^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
if [ $# -eq 1 ];then
        if echo $1|grep -E -q $reg ;then
                dateStr=$1
        else
				exit 1
                echo '参数格式不对'
        fi
else
        dateStr=`date -d '-1 day' +%F`
fi
echo $dateStr

# 1- SQOOP IMPORT
echo '---------------SQOOP IMPORT START---------------'
SQOOP_HOME=/usr/bin/sqoop
# PUBLIC PATH
url=${url}
username='itcast_zx_stu'
password='itcast_zx_stu'


${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from customer_relationship where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'customer_relationship' \
--fields-terminated-by '\t' \
-m 1

wait

${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from itcast_clazz where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'itcast_clazz' \
--fields-terminated-by '\t' \
-m 1

wait

${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from employee where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'employee' \
--fields-terminated-by '\t' \
-m 1

wait

${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from scrm_department where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'scrm_department' \
--fields-terminated-by '\t' \
-m 1

wait

${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from customer_clue where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'customer_clue' \
--fields-terminated-by '\t' \
-m 1

wait

${SQOOP_HOME} import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect ${url} \
--username ${username} \
--password ${password} \
--query "select  *, '${dateStr}' as dt from customer_appeal where 1=1 and \$CONDITIONS" \
--hcatalog-database 'zx_ods' \
--hcatalog-table 'customer_appeal' \
--fields-terminated-by '\t' \
-m 1


echo '---------------SQOOP IMPORT SUCCESS---------------'

export HIVE_HOME=/usr/bin/hive

hiveSql_dwd = "


SET hive.exec.dynamic.partition=true; 
SET hive.exec.dynamic.partition.mode=nonstrict; 
set hive.exec.max.dynamic.partitions.pernode=10000;  
set hive.exec.max.dynamic.partitions=100000; 
set hive.exec.max.created.files=150000;


set hive.exec.compress.intermediate=true; 
set hive.exec.compress.output=true; 


set hive.exec.orc.compression.strategy=COMPRESSION; 

set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true;


INSERT overwrite table zx_dwd.customer_relationship partition(dt)
select 
id,
create_date_time,
update_date_time,
deleted,
customer_id,
first_id,
belonger,
belonger_name,
initial_belonger,
distribution_handler,
business_scrm_department_id,
last_visit_time,
next_visit_time,
origin_type,
itcast_school_id,
itcast_subject_id,
intention_study_type,
anticipat_signup_date,
level,
creator,
current_creator,
creator_name,
origin_channel,
comment,
first_customer_clue_id,
last_customer_clue_id,
process_state,
process_time,
payment_state,
payment_time,
signup_state,
signup_time,
notice_state,
notice_time,
lock_state,
lock_time,
itcast_clazz_id,
itcast_clazz_time,
payment_url,
payment_url_time,
ems_student_id,
delete_reason,
deleter,
deleter_name,
delete_time,
course_id,
course_name,
delete_comment,
close_state,
close_time,
appeal_id,
tenant,
total_fee,
belonged,
belonged_time,
belonger_time,
transfer,
transfer_time,
follow_type,
transfer_bxg_oa_account,
transfer_bxg_belonger_name,
'9999-99-99' as end_time,
case when origin_type in ('NETSERVICE','PRESIGNUP') then '0'
else '1' end as origin_type_stat,
HOUR(payment_time) as payment_time_hour,
year(payment_time) as payment_time_year,
MONTH(payment_time)payment_time_month,
to_date(payment_time)payment_time_month,
dt
from zx_ods.customer_relationship 
where  deleted = 0 and customer_id is not null and payment_state = 'PAID';

INSERT overwrite table zx_dwd.customer_relationship_1 partition(dt)
select 
id,
create_date_time,
update_date_time,
deleted,
customer_id,
first_id,
belonger,
belonger_name,
initial_belonger,
distribution_handler,
business_scrm_department_id,
last_visit_time,
next_visit_time,
origin_type,
itcast_school_id,
itcast_subject_id,
intention_study_type,
anticipat_signup_date,
level,
creator,
current_creator,
creator_name,
origin_channel,
comment,
first_customer_clue_id,
last_customer_clue_id,
process_state,
process_time,
payment_state,
payment_time,
signup_state,
signup_time,
notice_state,
notice_time,
lock_state,
lock_time,
itcast_clazz_id,
itcast_clazz_time,
payment_url,
payment_url_time,
ems_student_id,
delete_reason,
deleter,
deleter_name,
delete_time,
course_id,
course_name,
delete_comment,
close_state,
close_time,
appeal_id,
tenant,
total_fee,
belonged,
belonged_time,
belonger_time,
transfer,
transfer_time,
follow_type,
transfer_bxg_oa_account,
transfer_bxg_belonger_name,
'9999-99-99' as end_time,
case when origin_type in ('NETSERVICE','PRESIGNUP') then '0'
else '1' end as origin_type_stat,
year(create_date_time) as create_date_year,
MONTH(create_date_time) as create_date_month,
to_date(create_date_time) as create_date_day,
dt
from zx_ods.customer_relationship 
where deleted = 0 and customer_id is not null ;

insert overwrite table zx_dwd.customer_relationship_2 partition(dt)
SELECT
cr.id,
cr.create_date_time,
cr.update_date_time,
cr.deleted,
cc.customer_id,
cr.first_id,
cr.belonger,
cr.belonger_name,
cr.initial_belonger,
cr.distribution_handler,
cr.business_scrm_department_id,
cr.last_visit_time,
cr.next_visit_time,
cr.origin_type,
cr.itcast_school_id,
cr.itcast_subject_id,
cr.intention_study_type,
cr.anticipat_signup_date,
cr.level,
cr.creator,
cr.current_creator,
cr.creator_name,
cr.origin_channel,
cr.comment,
cr.first_customer_clue_id,
cr.last_customer_clue_id,
cr.process_state,
cr.process_time,
cr.payment_state,
cr.payment_time,
cr.signup_state,
cr.signup_time,
cr.notice_state,
cr.notice_time,
cr.lock_state,
cr.lock_time,
cr.itcast_clazz_id,
cr.itcast_clazz_time,
cr.payment_url,
cr.payment_url_time,
cr.ems_student_id,
cr.delete_reason,
cr.deleter,
cr.deleter_name,
cr.delete_time,
cr.course_id,
cr.course_name,
cr.delete_comment,
cr.close_state,
cr.close_time,
cr.appeal_id,
cr.tenant,
cr.total_fee,
cr.belonged,
cr.belonged_time,
cr.belonger_time,
cr.transfer,
cr.transfer_time,
cr.follow_type,
cr.transfer_bxg_oa_account,
cr.transfer_bxg_belonger_name,
'9999-99-99' as end_time,
case when cr.origin_type in ('NETSERVICE','PRESIGNUP') then '0'
else '1' end as origin_type_stat,
year(cr.create_date_time) as create_date_year,
MONTH(cr.create_date_time) as create_date_month,
to_date(cr.create_date_time) as create_date_day,
cr.dt
from  (SELECT * from zx_ods.customer_clue where deleted = 'false' and clue_state IN ( 'VALID_NEW_CLUES',	'VALID_PUBLIC_NEW_CLUE' )) cc
left join (select * from zx_ods.customer_relationship cr where cr.id not in (SELECT customer_relationship_first_id from zx_ods.customer_appeal where appeal_status = '1' )) cr
ON cr.id = cc.customer_relationship_id ;

INSERT overwrite table zx_dwd.itcast_clazz partition(dt)
SELECT 
id,
create_date_time,
update_date_time,
deleted,
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
itcast_brand,
clazz_type_state,
clazz_type_name,
teaching_mode,
start_time,
end_time,
comment,
detail,
uncertain,
tenant,
'9999-99-99' as ends_time,
dt
FROM zx_ods.itcast_clazz;

"
${HIVE_HOME} -S -e "${hiveSql_dwd}"

echo 'import zx_dwd success'


hiveSql_dwb="

SET hive.exec.dynamic.partition=true; 
SET hive.exec.dynamic.partition.mode=nonstrict; 
set hive.exec.max.dynamic.partitions.pernode=10000;  
set hive.exec.max.dynamic.partitions=100000; 
set hive.exec.max.created.files=150000;


set hive.exec.compress.intermediate=true; 
set hive.exec.compress.output=true; 


set hive.exec.orc.compression.strategy=COMPRESSION; 

set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true;

insert overwrite table zx_dwb.customer_signup_dwb partition(dt)
select
	cr.create_date_time,
	cr.customer_id ,
	ic.itcast_school_id,
	ic.itcast_school_name ,
	ic.itcast_subject_id ,
	ic.itcast_subject_name ,
	e.tdepart_id ,
	d.name as tdepart_name ,
	cr.origin_type ,
	cr.origin_type_stat ,

	cr.payment_time_year , 
	cr.payment_time_month , 

	cr.payment_time_hour ,
	cr.payment_time_day as  dt
from (select * from zx_dwd.customer_relationship where end_time = '9999-99-99') cr
LEFT JOIN zx_dwd.itcast_clazz  ic on cr.itcast_clazz_id=ic.id and ic.ends_time='9999-99-99'
LEFT JOIN zx_ods.employee e on cr.creator=e.id 
left join zx_ods.scrm_department d on e.tdepart_id = d.id;

INSERT overwrite table zx_dwb.customer_signup_dwb_1 partition(dt)
SELECT 
	cr1.create_date_time ,
    cr1.customer_id as t_customer_id ,
	cr2.customer_id as p_customer_id ,
	cr1.origin_type_stat ,
	cr1.create_date_year ,
	cr1.create_date_month ,
	cr1.create_date_day,
	cr1.create_date_day as dt
FROM (select * from zx_dwd.customer_relationship_1 where end_time = '9999-99-99' ) cr1
left join (select * from zx_dwd.customer_relationship_1 where end_time = '9999-99-99' and payment_state = 'PAID' ) cr2
on cr1.id = cr2.id;

INSERT overwrite table zx_dwb.customer_signup_dwb_2 partition(dt)
SELECT 
	cr1.create_date_time ,
    cr1.customer_id as t_customer_id ,
	cr2.customer_id as p_customer_id ,
	cr1.origin_type_stat ,
	cr1.create_date_year ,
	cr1.create_date_month ,
	cr1.create_date_day,
	cr1.create_date_day as dt

FROM (select * from zx_dwd.customer_relationship_2 where end_time = '9999-99-99' ) cr1
full join (select * from zx_dwd.customer_relationship_1 where end_time = '9999-99-99' and payment_state = 'PAID' ) cr2
on cr1.create_date_day = cr2.create_date_day;

"

${HIVE_HOME} -S -e "${hiveSql_dwb}"

echo 'import zx_dwb success'


hiveSql_dws="

SET hive.exec.dynamic.partition=true; 
SET hive.exec.dynamic.partition.mode=nonstrict; 
set hive.exec.max.dynamic.partitions.pernode=10000;  
set hive.exec.max.dynamic.partitions=100000; 
set hive.exec.max.created.files=150000;


set hive.exec.compress.intermediate=true; 
set hive.exec.compress.output=true; 


set hive.exec.orc.compression.strategy=COMPRESSION; 

set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true;


INSERT  into hive.zx_dws.customer_signup_dws
select
-- 指标
count(customer_id) as signup_num,

-- 维度
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
tdepart_id,
tdepart_name,
origin_type,
origin_type_stat,
    case    when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id )=111
            then  'year_school'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=175
            then  'month_school'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=207
            then  'day_school'--day

            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=109
            then  'year_ots_school'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=173
            then  'month_ots_school'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=205
            then 'day_ots_school'--day


            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=117
            then  'year_ots_sub'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=181
            then  'month_ots_sub'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=213
            then  'day_ots_sub'--day
--4
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=101
            then  'year_ots_shool_sub'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=165
            then  'month_ots_shool_sub'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=197
            then  'day_ots_shool_sub'--day
--5
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=121
            then  'year_ots_oc'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=185
            then  'month_ots_oc'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=217
            then  'day_ots_oc'--day
--6
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=124
            then  'year_ots_t_id'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=188
            then  'month_ots_t_id'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=220
            then  'day_ots_t_id'--day
            else null end as groupType,

payment_time_year,
payment_time_month,
dt
from hive.zx_dwb.customer_signup_dwb
group by
grouping sets  (
--天
     (dt,itcast_school_id,itcast_school_name), -- 日期+校区
     (dt,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (dt,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (dt,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (dt,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (dt,origin_type_stat,tdepart_id,tdepart_name), -- 日期+线上线下+咨询中心


-- 月
     (payment_time_month,itcast_school_id,itcast_school_name), -- 日期+校区
     (payment_time_month,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (payment_time_month,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (payment_time_month,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (payment_time_month,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (payment_time_month,origin_type_stat,tdepart_id,tdepart_name), -- 日期+线上线下+咨询中心


-- 年
     (payment_time_year,itcast_school_id,itcast_school_name), -- 日期+校区
     (payment_time_year,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (payment_time_year,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (payment_time_year,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (payment_time_year,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (payment_time_year,origin_type_stat,tdepart_id,tdepart_name) -- 日期+线上线下+咨询中心
);


"

${HIVE_HOME} -S -e "${hiveSql_dws}"

echo 'import zx_dws success'


hiveSql_dm="

SET hive.exec.dynamic.partition=true; 
SET hive.exec.dynamic.partition.mode=nonstrict; 
set hive.exec.max.dynamic.partitions.pernode=10000;  
set hive.exec.max.dynamic.partitions=100000; 
set hive.exec.max.created.files=150000;


set hive.exec.compress.intermediate=true; 
set hive.exec.compress.output=true; 


set hive.exec.orc.compression.strategy=COMPRESSION; 

set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true;

INSERT  into hive.zx_dm.customer_signup_dm
select
-- 指标
count(customer_id) as signup_num,

-- 维度
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
tdepart_id,
tdepart_name,
origin_type,
origin_type_stat,
    case    when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id )=111
            then  'year_school'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=175
            then  'month_school'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=207
            then  'day_school'--day

            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=109
            then  'year_ots_school'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=173
            then  'month_ots_school'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=205
            then 'day_ots_school'--day


            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=117
            then  'year_ots_sub'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=181
            then  'month_ots_sub'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=213
            then  'day_ots_sub'--day
--4
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=101
            then  'year_ots_shool_sub'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=165
            then  'month_ots_shool_sub'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=197
            then  'day_ots_shool_sub'--day
--5
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=121
            then  'year_ots_oc'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=185
            then  'month_ots_oc'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=217
            then  'day_ots_oc'--day
--6
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=124
            then  'year_ots_t_id'--year
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=188
            then  'month_ots_t_id'--month
            when grouping(payment_time_year,payment_time_month,dt,itcast_school_id,itcast_subject_id,origin_type,origin_type_stat,tdepart_id)=220
            then  'day_ots_t_id'--day
            else null end as groupType,

payment_time_year,
payment_time_month,
dt
from hive.zx_dwb.customer_signup_dwb
group by
grouping sets  (
--天
     (dt,itcast_school_id,itcast_school_name), -- 日期+校区
     (dt,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (dt,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (dt,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (dt,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (dt,origin_type_stat,tdepart_id,tdepart_name), -- 日期+线上线下+咨询中心


-- 月
     (payment_time_month,itcast_school_id,itcast_school_name), -- 日期+校区
     (payment_time_month,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (payment_time_month,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (payment_time_month,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (payment_time_month,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (payment_time_month,origin_type_stat,tdepart_id,tdepart_name), -- 日期+线上线下+咨询中心


-- 年
     (payment_time_year,itcast_school_id,itcast_school_name), -- 日期+校区
     (payment_time_year,origin_type_stat,itcast_school_id,itcast_school_name), -- 日期+线上线下+校区
     (payment_time_year,origin_type_stat,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
     (payment_time_year,origin_type_stat,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), -- 日期+线上线下+校区+学科
     (payment_time_year,origin_type_stat,origin_type ), -- 日期+线上线下+来源渠道
     (payment_time_year,origin_type_stat,tdepart_id,tdepart_name) -- 日期+线上线下+咨询中心
);

insert overwrite table zx_dm.customer_signup_dm_1 partition(dt)
SELECT
	count(t_customer_id),
	count(p_customer_id),
	origin_type_stat,
	case
		when grouping__id in (9,1) then 'year'
		when grouping__id in (11,3) then 'month'
		when grouping__id in (15,7) then 'day'
	end as time_type,
	create_date_year ,
	create_date_month ,
	create_date_day,
	'2022-10-17' as dt
from
	zx_dwb.customer_signup_dwb_1
group by
	create_date_year,	create_date_month,	create_date_day,	origin_type_stat 
	
	GROUPING SETS ( 
	(create_date_year, create_date_month, create_date_day, origin_type_stat),
	( create_date_year, create_date_month, origin_type_stat),
	(create_date_year, origin_type_stat),

	(create_date_year, create_date_month, create_date_day),
	( create_date_year, create_date_month),
	(create_date_year)
);

insert overwrite table zx_dm.customer_signup_dm_2 partition(dt)
SELECT
	count(t_customer_id),
	count(p_customer_id),
	origin_type_stat,
	case
		when grouping__id in (9,1) then 'year'
		when grouping__id in (11,3) then 'month'
		when grouping__id in (15,7) then 'day'
	end as time_type,
	create_date_year ,
	create_date_month ,
	create_date_day,
	'2022-10-17' as dt
from
	zx_dwb.customer_signup_dwb_2
group by
	create_date_year,	create_date_month,	create_date_day,	origin_type_stat 
	
	GROUPING SETS ( 
	(create_date_year, create_date_month, create_date_day, origin_type_stat),
	( create_date_year, create_date_month, origin_type_stat),
	(create_date_year, origin_type_stat),

	(create_date_year, create_date_month, create_date_day),
	( create_date_year, create_date_month),
	(create_date_year)
);

"


${HIVE_HOME} -S -e "${hiveSql_dm}"

echo 'import zx_dm success'

hiveSql_result="

CREATE  TABLE  mysql.zx_result.customer_signup_dm
as  select  * from hive.zx_dm.customer_signup_dm;

CREATE  TABLE  mysql.zx_result.customer_signup_dm_1
as  select  * from hive.zx_dm.customer_signup_dm_1;

CREATE  TABLE  mysql.zx_result.customer_signup_dm_2
as  select  * from hive.zx_dm.customer_signup_dm_2;


echo 'export zx_dm success'